package parser;

import java.io.File;
import java.io.FileReader;
import java.io.LineNumberReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class FileParser {

	public static void main(String[] args) {
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		ResultSet rs2 = null;
		try {
			Class.forName( "com.mysql.jdbc.Driver" );
			conn = DriverManager.getConnection(
				     "jdbc:mysql://localhost/sffDraftGameTest",
				     "root",
				     "password" );

		}
		catch (Exception e) {
			e.printStackTrace();
		}
		
		File file = null;
		FileReader freader = null;
		LineNumberReader lnreader = null;
		try {
			file = new File("c:/Document1.txt");
			freader = new FileReader(file);
			lnreader = new LineNumberReader(freader);
			String line = "";
			List<String> players = new ArrayList<String>();
			while ((line = lnreader.readLine()) != null) {
				String[] elements = line.split("#");
				String[] name = elements[0].split("~");
				String surname = name[0].trim().replace("'", "''");
				String firstName = name[1].trim().replace("'", "''");
				String team = elements[1].trim();
				
				players.add(firstName + "~" + surname + "~" + team);
			}
			
			stmt = conn.createStatement();
			rs = stmt.executeQuery( "SELECT firstName, surname, team FROM Player");
			while (rs.next()) {
				String firstName = rs.getString(1).replace("'", "''");
				String surname = rs.getString(2).replace("'", "''");
				String team = rs.getString(3);
				String player = firstName + "~" + surname + "~" + team;
				if (!players.contains(player)) {
					System.out.println("DELETE from Player where firstName = '" + firstName + "' and surname = '" + surname + "' and team = '" + team + "';");
				}
			}

			rs.close();
			stmt.close();
		}
		catch (Exception e) {
			e.printStackTrace();
		}
		finally {
			try {
				freader.close();
				lnreader.close();
				rs.close();
				//rs2.close();
				stmt.close();
				conn.close();
			}
			catch (Exception e) {
				e.printStackTrace();
			}
		}
	}
}
